unit StateForm;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, DBModule, Vcl.StdCtrls, Vcl.Mask, IB,
  Vcl.DBCtrls, Vcl.ExtCtrls, Vcl.Grids, Vcl.DBGrids, IBSQL, UserUtils, Data.DB,
  IBCustomDataSet, IBTable, IBDatabase;

type
  TfmState = class(TForm)
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    DBGrid1: TDBGrid;
    btnClose: TButton;
    txtState: TEdit;
    txtAbbr: TEdit;
    btnNew: TButton;
    btnDelete: TButton;
    btnSave: TButton;
    btnRefresh: TButton;
    dbcbCountry: TDBLookupComboBox;
    chkFiltered: TCheckBox;
    IBDatabase1: TIBDatabase;
    IBTransaction1: TIBTransaction;
    fbCountry: TIBTable;
    dsCountry: TDataSource;
    dsState: TDataSource;
    fbState: TIBTable;
    procedure btnCloseClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure btnNewClick(Sender: TObject);
    procedure btnDeleteClick(Sender: TObject);
    procedure DBGrid1DblClick(Sender: TObject);
    procedure btnSaveClick(Sender: TObject);
    procedure btnRefreshClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure DBGrid1TitleClick(Column: TColumn);
    procedure chkFilteredClick(Sender: TObject);
    procedure txtStateEnter(Sender: TObject);
    procedure txtAbbrEnter(Sender: TObject);
  private
    { Private declarations }
    Operation: TOperation;
    procedure SetFilter(isFiltered: Boolean);
  public
    { Public declarations }
  end;

var
  fmState: TfmState;

implementation

{$R *.dfm}

procedure TfmState.btnDeleteClick(Sender: TObject);
begin
  with DataModule1.qrySQL do
  begin
    SQL.Clear;
    Params.Clear;
    try
      try
        SQL.Add('delete from State where ID=:ID');
        Params.ParamByName('ID').AsString := DBGrid1.DataSource.DataSet.FieldByName('ID').AsString;
        ExecSQL;
        Transaction.Commit;
      except
        on E: EIBInterBaseError do
        begin
          Transaction.Rollback;
          ShowMessage(Format('IBErrorCode=%d, SQLCode=%d, Msg=%s, StackTrace=%s', [E.IBErrorCode, E.SQLCode, E.Message, E.StackTrace]));
        end;
      end;
    finally
      btnRefresh.Click;
    end;
  end;
end;

procedure TfmState.btnNewClick(Sender: TObject);
begin
  dbcbCountry.KeyValue := '';
  txtState.Text := '';
  txtAbbr.Text := '';
  Operation := opNew;
end;

procedure TfmState.btnRefreshClick(Sender: TObject);
begin
//  RefreshCountryStateCityDB(Self);
  IBDatabase1.DatabaseName := DataModule1.IBDatabase1.DatabaseName;
  IBDatabase1.Connected := True;
  IBTransaction1.Active := True;
  with fbCountry do
  begin
    if not Active then
      Open;
    Last;
    First;
  end;
  with fbState do
  begin
    if not Active then
      Open;
    Last;
    First;
  end;
end;

procedure TfmState.btnSaveClick(Sender: TObject);
begin
  if (dbcbCountry.Text <> '') and (txtState.Text <> '') and (txtAbbr.Text <> '') then
    try
      with DataModule1.qrySQL do
      begin
        try
          SQL.Clear;
          case Operation of
            opNew: begin
              SQL.Add('insert into State (abbriviation, state, country_abbriviation) values (:Abbr, ' +
                      ':State, :CountryAbbr)');
            end;
            opEdit: begin
              SQL.Add('update State set abbriviation=:Abbr, state=:State, country_abbriviation=:CountryAbbr ' +
                      'where ID=:ID');
              Params.ParamByName('ID').AsString := DBGrid1.DataSource.DataSet.FieldByName('ID').AsString;
            end;
          end;
          Params.ParamByName('Abbr').AsString := txtAbbr.Text;
          Params.ParamByName('State').AsString := txtState.Text;
          Params.ParamByName('CountryAbbr').AsString := dbcbCountry.KeyValue;
          if not Prepared then
            Prepare;
          ExecSQL;
          Transaction.Commit;
        except
          on E: EIBInterBaseError do
          begin
            Transaction.Rollback;
            ShowMessage(Format('IBErrorCode=%d, SQLCode=%d, Msg=%s, StackTrace=%s', [E.IBErrorCode, E.SQLCode, E.Message, E.StackTrace]));
          end;
        end;
      end;
    finally
      btnRefresh.Click;
      btnNew.Click;
    end
  else
    ShowMessage('All fields are required.');
end;

procedure TfmState.chkFilteredClick(Sender: TObject);
begin
  SetFilter(chkFiltered.Checked);
end;

procedure TfmState.btnCloseClick(Sender: TObject);
begin
  Close;
end;

procedure TfmState.DBGrid1DblClick(Sender: TObject);
begin
  dbcbCountry.KeyValue := DBGrid1.DataSource.DataSet.FieldByName('COUNTRY_ABBRIVIATION').AsString;
  txtAbbr.Text := DBGrid1.DataSource.DataSet.FieldByName('ABBRIVIATION').AsString;
  txtState.Text := DBGrid1.DataSource.DataSet.FieldByName('STATE').AsString;
  Operation := opEdit;
end;

procedure TfmState.DBGrid1TitleClick(Column: TColumn);
begin
  fbState.IndexFieldNames := 'COUNTRY_ABBRIVIATION;' + Column.FieldName;
end;

procedure TfmState.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  SetFilter(True);
  FreeAndNil(fmState);
end;

procedure TfmState.FormCreate(Sender: TObject);
begin
  btnRefresh.Click;
  Operation := opNew;
end;

procedure TfmState.SetFilter(isFiltered: Boolean);
begin
  with fbState do
  begin
    Close;
    if isFiltered then
    begin
      MasterSource := dsCountry;
      MasterFields := 'ABBRIVIATION';
      IndexFieldNames := 'COUNTRY_ABBRIVIATION';
    end else begin
      MasterFields := '';
      MasterSource := nil;
      IndexFieldNames := '';
    end;
    Open;
    Last;
    First;
  end;
end;

procedure TfmState.txtAbbrEnter(Sender: TObject);
begin
  txtAbbr.SelectAll;
end;

procedure TfmState.txtStateEnter(Sender: TObject);
begin
  txtState.SelectAll;
end;

end.
